var express = require('express');
var router = express.Router();
//引入Query.js连接数据库
const Query = require('../tools/Query')

//地区
router.get('/areas', async (req, res, next) => {
    //声明查询
    let areas = `SELECT * FROM e_area`
    let [err, result] = await Query(areas)
    //声明数据为空对象

    let data = {}
    if (!err) {
        data = {
            msg: 'ok',
            status: 200,
            result
        }
    } else {
        data = {
            msg: err,
        }
    }
    //
    res.send(data)
})

//年级

router.get('/grades', async (req, res, next) => {
    //声明查询
    let grades = `SELECT * FROM e_grade`
    let [err, result] = await Query(grades)
    //数据
    let data = {}
    //判断
    if (!err) {
        data = {
            msg: 'ok',
            status: 200,
            result
        }
    } else {
        data = {
            msg: err
        }
    }
    //返回数据
    res.send(data)
})

//学科
router.get('/subjects', async (req, res, next) => {
    //查询
    let subjects = `SELECT * FROM e_subject`
    let [err, result] = await Query(subjects)
    //数据
    let data = {}
    //判断
    if (!err) {
        data = {
            msg: 'ok',
            status: 200,
            result
        }
    } else {
        data = {
            msg: err
        }
    }
    //返回数据
    res.send(data)
})
//考试类别
router.get('/cates', async (req, res, next) => {
    //查询
    let cates = `SELECT * FROM e_category`
    let [err, result] = await Query(cates)
    //数据
    let data = {}
    //判断
    if (!err) {
        data = {
            msg: 'ok',
            status: 200,
            result
        }
    } else {
        data = {
            msg: err
        }
    }
    //返回数据
    res.send(data)
})

//分页
router.get('/course', async (req, res) => {
    //结构出前端获取来的值
    let {pageSize = '6', page = 1, area = '', grade = '', subject = '', cate = '',} = req.query

    //处理数据类型
    pageSize = Number(pageSize)
    page = Number(page)
        //从哪一页开始截取 (直接使用公式)
        let startIndex = (page - 1) * pageSize
    //限定条件
    let whereStr = ''


    //判断如果whereStr的长度为0就直接写入内容
    if (whereStr.length == 0) {
        //判断如果area不为空就直接写入
        if (area !== '') {
            whereStr += `area_name='${area}'`
        }
    } else {
        if (area !== '') {
            whereStr += `AND area_name='${area}'`
        }
    }

    //判断如果where长度为0直接写入
    if (whereStr.length == 0) {
        if (grade !== '') {
            whereStr += `grade_name='${grade}'`
        }
    } else {
        if (grade !== '') {
            whereStr += ` AND grade_name='${grade}'`
        }
    }

    //学科 如果where长度为0直接写入  否则AND 内容
    if (whereStr.length == 0) {
        if (subject !== '') {
            whereStr += `subject_name='${subject}'`
        }
    } else {
        if (subject !== '') {
            whereStr += ` AND subject_name='${subject}'`
        }
    }

    //考试类型  如果where长度为0 那么直接写入 否则AND 内容
    if (whereStr.length == 0) {
        //如果值不为空就写入
        if (cate != '') {
            whereStr += `category_name='${cate}'`
        }
    } else {
        if (cate !== '') {
            whereStr += ` AND category_name='${cate}'`
        }
    }
    /* 
            如果每页3条，共10条
        let pageSize = 3
        let totalRow = 10
        let totalPage = Math.ceil(totalRow/pageSize)  => 4
        let page = 1 //当前的页码
        第1页： LIMIT 0,pageSize    0=(page-1)*pageSize
        第2页： LIMIT 3,pageSize    3=(page-1)*pageSize
        第3页： LIMIT 6,pageSize    6=(page-1)*pageSize
    */
    //在查询之前判断where里有没有内容 没有就让where语句为空 否则才加

    if (whereStr != '') { //如果这个条件变量不为空，才加 WHERE 关键字到whereStr变量中。
        whereStr = `WHERE ${whereStr}`
      }
    // if (whereStr.length == 0) {
    //     whereStr = ''
    // } else {
    //     whereStr = 'WHERE ' + whereStr
    // }

    //获取表中总页数的长度
    let sql1 = `SELECT * FROM e_course ${whereStr}`
    // //从表中截取数据 限制从哪里开始 每页几条数据
    let sql2 = `SELECT id,cid,area_name,subject_name,title,CONCAT('http://localhost:3000/uploads/course/',image_src)AS image_src FROM e_course ${whereStr} ORDER BY id DESC LIMIT ${startIndex},${pageSize}`
    //解构获取长度
    let [, result1] = await Query(sql1)
    //接收数据
    let [err, result2] = await Query(sql2)
    //总条数
    let totalRow = result1.length
    //总页数
    let totalPage = Math.ceil(totalRow / pageSize) //总页数  通过总数据除以每页多少数据算出

    console.log(totalPage);


    

    let data = {
        status: 200,
        msg: 'ok',
        result2,
        page,
        pageSize,
        totalPage
    }

    res.send(data)

})

// router.get('allCourse',async(req,res)=>{
//     //获取所有课程
// })

//搜索

router.get('/searchlist', async (req, res) => {
      //结构出前端获取来的值
      let {pageSize = '6', page = 1, keyword = '寒假'} = req.query

      //处理数据类型
      pageSize = Number(pageSize)
      page = Number(page)
      //从哪一页开始截取 (直接使用公式)
      let startIndex = (page - 1) * pageSize
      //限定条件
      let whereStr = ''
  
      //获取表中总页数的长度
      let sql1 = `SELECT * FROM e_course ${whereStr}`
      // //从表中截取数据 限制从哪里开始 每页几条数据
      let sql2 = `SELECT id,cid,area_name,subject_name,title,CONCAT('http://localhost:3000/uploads/course/',image_src)AS image_src FROM e_course WHERE title LIKE '%${keyword}%' ORDER BY id DESC LIMIT ${startIndex},${pageSize}`
    //   let sql2 = `SELECT id,cid,title,price,CONCAT('${api_url}','uploads/course/',image_src) AS image_src,intro,type,type_name,area_name,grade_name,subject_name,category_name,status FROM e_course WHERE title LIKE '%${keyword}%' ORDER BY id DESC LIMIT ${startIndex},${pageSize}`
      //解构获取长度
      let [, result1] = await Query(sql1)
      //接收数据
      let result2 = await Query(sql2)
    //   console.log(result2);
      //总条数
      let totalRow = result1.length
      //总页数
      let totalPage = Math.ceil(totalRow / pageSize) //总页数  通过总数据除以每页多少数据算出
      let data = {
          status: 200,
          msg: 'ok',
          result2,
          page,
          pageSize,
          totalPage,
          keyword,
          totalRow
      }
  
      res.send(data)
})





module.exports = router;